package com.pur.zsms.matermanage.mater.dao.impl;

import java.math.BigDecimal;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.matermanage.mater.dao.IMaterDao;
import com.pur.zsms.matermanage.mater.po.CheckMaterCostPo;
import com.pur.zsms.matermanage.mater.po.CheckMaterSalePo;
import com.pur.zsms.matermanage.mater.po.MaterLevelBlPo;
import com.pur.zsms.matermanage.mater.po.MaterLevelCblPo;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.po.MaterPo;
import com.pur.zsms.matermanage.mater.po.MaterTypePo;
import com.pur.zsms.matermanage.mater.po.ShoppingCartMaterPo;
import com.pur.zsms.matermanage.mater.po.ShoppingCartMaterSelfPo;
import com.pur.zsms.matermanage.mater.po.ShoppingCartMaterSupplierPo;
import com.pur.zsms.matermanage.mater.po.UnitMaterConvertPo;
import com.pur.zsms.matermanage.mater.vo.MaterTypeVo;
import com.pur.zsms.matermanage.mater.vo.MaterValueVo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.matermanage.mater.vo.MaterVoForSup;
import com.pur.zsms.matermanage.mater.vo.MaterVoForUnit;
import com.pur.zsms.matermanage.mater.vo.ShoppingCartMaterVo;

@Repository("materDao")
@Transactional
public class materDaoImpl extends GenericDao implements IMaterDao {

	public int addMater(MaterPo c) throws GenericException {
		this.merge(c);
		List quert = this.entityManager.createNativeQuery("SELECT LAST_INSERT_ID()").getResultList();
		return Integer.valueOf(quert.get(0).toString());
	}

	public void addMaterTypePo(MaterTypePo c) throws GenericException {
		this.persist(c);
	}

	public void updateMaterTypePo(MaterTypePo c) throws GenericException {
		this.merge(c);
	}

	public void addcheckMaterCostPo(CheckMaterCostPo c) throws GenericException {
		this.persist(c);
	}

	public void addUnitMaterConvertPo(UnitMaterConvertPo c) throws GenericException {
		this.persist(c);
	}

	public void updateUnitMaterConvertPo(UnitMaterConvertPo c) throws GenericException {
		this.merge(c);
	}

	public void addcheckMaterSalePo(CheckMaterSalePo c) throws GenericException {
		this.persist(c);
	}

	public void updatecheckMaterSalePo(CheckMaterSalePo c) throws GenericException {
		this.merge(c);
	}

	public void updatecheckMaterCostPo(CheckMaterCostPo c) throws GenericException {
		this.merge(c);

	}

	public void addShoppingCart(ShoppingCartMaterPo c) throws GenericException {
		this.persist(c);
	}

	public void updateShoppingCart(ShoppingCartMaterPo c) throws GenericException {
		this.merge(c);
	}

	public void addShoppingCartSelf(ShoppingCartMaterSelfPo c) throws GenericException {
		this.persist(c);
	}

	public void addShoppingCartSup(ShoppingCartMaterSupplierPo c) throws GenericException {
		this.persist(c);
	}

	public void deleteMaterUnit(int materId, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UnitMaterConvertPo o where o.supId = '" + supId + "' and ( o.hostMaterId=" + materId + " OR o.auxiliaryMaterId='"
				+ materId + "')");
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartOne(ShoppingCartMaterVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterPo o where o.sid=" + c.getSid());
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartOneSelf(ShoppingCartMaterVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterSelfPo o where o.sid=" + c.getSid());
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartSup(ShoppingCartMaterVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterSupplierPo o where o.sid=" + c.getSid());
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartSupAll(int c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterSupplierPo o where o.supId=" + c);
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartForCust(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterPo o where o.custId=" + shopId);
		template.executeUpdate(ql.toString());
	}

	public void deleteShoppingCartForCustSelf(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShoppingCartMaterSelfPo o where o.custId=" + shopId);
		template.executeUpdate(ql.toString());
	}

	public void updateMater(MaterPo c) throws GenericException {
		this.merge(c);
	}

	public void updateMaterLevel(MaterLevelPo c) throws GenericException {
		this.merge(c);
	}

	public void addMaterLevel(MaterLevelPo c) throws GenericException {
		this.persist(c);
	}

	public void addMaterLevelPersist(MaterLevelPo c) throws GenericException {
		this.persist(c);
	}

	public void addMaterLevelBl(MaterLevelBlPo c) throws GenericException {
		this.persist(c);
	}

	public void addMaterLevelCbl(MaterLevelCblPo c) throws GenericException {
		this.persist(c);
	}

	public void updateMaterLevelBl(MaterLevelBlPo c) throws GenericException {
		this.merge(c);
	}

	public void updateMaterLevelCbl(MaterLevelCblPo c) throws GenericException {
		this.merge(c);
	}

	@Override
	public void deleteMaterLevel(MaterLevelPo materLevelPo) throws GenericException {
		this.merge(materLevelPo);
	}

	public void deleteMaterUnitHostMaterId(int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UnitMaterConvertPo o where o.hostMaterId = '" + materId + "' and o.supId='" + supId + "'");
		template.executeUpdate(ql.toString());
	}

	public void deleteMaterUnitAuxiliaryMaterId(int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UnitMaterConvertPo o where o.auxiliaryMaterId = '" + materId + "' and o.supId='" + supId + "'");
		template.executeUpdate(ql.toString());
	}

	public PaginationSupport listMater(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level materLevel,b.mater_value materValue,b.mater_proportion materProportion,b.mater_describe materDescribe,b.sup_id,c.sup_companyname,b.mater_value_undressed,f.unit,f.specifications,f.brands,b.delete_flag from t_material f,t_material_level b,t_supplier c  where c.sup_id = b.sup_id  and f.mater_id = b.mater_id");
		ql.append(" AND b.cust_id=0 ");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (materVo.getSupId() != 0) {
				ql.append(" and b.sup_id = '" + materVo.getSupId() + "'");
			}
		}
		ql.append(" order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listMaterUnit(PageController pageController, MaterVoForUnit materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT f.mater_id materId, f.mater_name materName, f.mater_type materType, b.mater_level materLevel, b.mater_value materValue, b.mater_proportion materProportion, b.mater_describe materDescribe, b.sup_id, c.sup_companyname, b.mater_value_undressed, f.unit, f.specifications, f.brands, b.delete_flag FROM u_unit_mater_convert a, t_material f, t_material_level b, t_supplier c WHERE a.host_mater_id = f.mater_id AND f.mater_id = b.mater_id AND a.sup_id = b.sup_id AND b.cust_id = '0' AND b.delete_flag != 1 AND c.sup_id = b.sup_id  ");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (materVo.getSupId() != 0) {
				ql.append(" and b.sup_id = '" + materVo.getSupId() + "'");
			}
		}
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport lookMaterUnit(PageController pageController, MaterVoForUnit materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT m.mater_id, m.mater_name, m.mater_type, m.unit, m.brands, m.specifications FROM t_material m , u_unit_mater_convert u WHERE m.mater_id = u.auxiliary_mater_id AND u.sup_id='"
				+ materVo.getSupId() + "' AND u.host_mater_id='" + materVo.getHostMaterId() + "'");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listMaterForSup(PageController pageController, MaterVoForSup materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level materLevel,b.mater_value materValue,b.mater_proportion materProportion,b.mater_describe materDescribe,b.sup_id,c.sup_companyname,b.mater_value_undressed,f.unit,f.specifications,f.brands,b.delete_flag,(SELECT co.`low_limit` FROM t_check_mater_cost co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id ) AS lowLimit ,(SELECT co.`upper_limit` FROM t_check_mater_cost co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id ) AS upperLimit,(SELECT co.`id` FROM t_check_mater_cost co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id ) as checkid , (SELECT co.`low_limit_sale` FROM t_check_mater_sale co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id) AS lowLimitSale, (SELECT co.`upper_limit_sale` FROM t_check_mater_sale co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id) AS upperLimitSale, (SELECT co.`id` FROM t_check_mater_sale co WHERE co.sup_id = c.sup_id AND co.mater_id = f.mater_id) AS checkidSale , (SELECT t_supplier.`sup_companyname` FROM t_supplier WHERE sup_id = (SELECT d.sup_id FROM t_distributor_supplier_mater d  WHERE d.mater_id = f.mater_id AND d.distributor_id = "
				+ materVo.getSupId()
				+ ")),(SELECT d.sup_id FROM t_distributor_supplier_mater d  WHERE d.mater_id = f.mater_id AND d.distributor_id = "
				+ materVo.getSupId() + ") from t_material f,t_material_level b,t_supplier c  where c.sup_id = b.sup_id  and f.mater_id = b.mater_id");
		ql.append(" AND b.cust_id=0 ");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (materVo.getSupId() != 0) {
				ql.append(" and b.sup_id = '" + materVo.getSupId() + "'");
			}
		}
		ql.append(" order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport lookMaterValue(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level materLevel,b.mater_value materValue,b.mater_proportion materProportion,b.mater_describe materDescribe,b.sup_id,c.sup_companyname,b.mater_value_undressed,b.cust_Id from t_material f,t_material_level_bl b,t_supplier c  where c.sup_id = b.sup_id  and f.mater_id = b.mater_id");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
		}
		ql.append(" AND b.sup_id = '" + materVo.getSupId() + "' AND b.`cust_id` IN ('" + materVo.getCustId() + "')");

		ql.append(" GROUP BY b.`mater_id` order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport lookMaterValueCost(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level materLevel,b.mater_value materValue,b.mater_proportion materProportion,b.mater_describe materDescribe,b.sup_id,c.sup_companyname,b.mater_value_undressed from t_material f,t_material_level_cbl b,t_supplier c  where c.sup_id = b.sup_id  and f.mater_id = b.mater_id");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
		}
		ql.append(" AND b.sup_id = '" + materVo.getSupId() + "'");
		ql.append(" group by  b.mater_id");

		ql.append(" order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listEditorMater(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select DISTINCT f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_proportion materProportion,b.mater_describe materDescribe from t_material f,t_material_level b where 1 =1 and f.mater_id = b.mater_id");
		ql.append(" AND b.mater_level = '1'");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
		}
		ql.append(" order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listSelectMater(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level , b.mater_value materValue,b.mater_describe materDescribe,b.mater_proportion,b.mid,b.sup_id,c.sup_companyname,b.mater_value_undressed,f.unit,f.specifications,f.brands from t_material f,t_material_level b,t_supplier c,t_shop_supplier_type d where c.sup_id =b.sup_id  and f.mater_id = b.mater_id ");
		ql.append(" AND b.`cust_id`=0 ");
		ql.append(" AND b.delete_flag = 0 ");
		if (materVo != null) {
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and (f.mater_name like '%" + materVo.getMaterName().trim() + "%' OR f.`all_spell` LIKE '%" + materVo.getMaterName()
						+ "%' OR f.`fist_spell` LIKE '%" + materVo.getMaterName() + "%')");
			}
			if (materVo.getMid() != 0) {
				ql.append(" and b.mid = '" + materVo.getMid() + "'");
			}
			if (materVo.getMaterId() != 0) {
				ql.append(" and f.mater_id = '" + materVo.getMaterId() + "'");
			}
		}

		// 供货商判断
		ql.append(" AND d.shop_id = '" + materVo.getCustId() + "' AND d.sup_type = f.mater_type AND b.sup_id = d.sup_id");
		ql.append(" order by f.mater_name desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findMaterTypeForSup(PageController pageController, MaterTypeVo vo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.mater_type,o.mater_type_name,o.order_id,o.parent,o.level,o.sup_id,o.is_Active FROM t_material_type o  WHERE o.sup_id ='"
				+ vo.getSupId() + "'");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<Object[]> listSelectMaterForMobile(MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level , b.mater_value materValue,b.mater_describe materDescribe,b.mater_proportion,b.mid,b.sup_id,c.sup_companyname,b.mater_value_undressed,f.unit,f.specifications,f.brands from t_material f,t_material_level b,t_supplier c,t_shop_supplier_type d,t_material_type g where c.sup_id =b.sup_id  and f.mater_id = b.mater_id ");
		ql.append(" AND b.`cust_id`=0 ");
		ql.append(" AND b.delete_flag = 0 ");
		ql.append(" AND g.`mater_type`=f.`mater_type`AND g.`sup_id`=c.`sup_id` ");

		if (materVo != null) {
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and (f.mater_name like '%" + materVo.getMaterName().trim() + "%' OR f.`all_spell` LIKE '%" + materVo.getMaterName()
						+ "%' OR f.`fist_spell` LIKE '%" + materVo.getMaterName() + "%')");
			}
			if (materVo.getMid() != 0) {
				ql.append(" and b.mid = '" + materVo.getMid() + "'");
			}
			if (materVo.getMaterId() != 0) {
				ql.append(" and f.mater_id = '" + materVo.getMaterId() + "'");
			}
			if (!"".equals(materVo.getSupIdAll()) && materVo.getSupIdAll() != null && !"0".equals(materVo.getSupIdAll())) {
				ql.append(" AND g.`sup_id` IN (" + materVo.getSupIdAll() + ") ");
			}
			if (!"".equals(materVo.getMaterTypeName()) && materVo.getMaterTypeName() != null) {
				ql.append(" AND g.`mater_type_name`='" + materVo.getMaterTypeName() + "'");
			}
		}

		// 供货商判断
		ql.append(" AND d.shop_id = '" + materVo.getCustId() + "' AND d.sup_type = f.mater_type AND b.sup_id = d.sup_id");
		ql.append(" order by f.mater_name desc LIMIT " + materVo.getLimit_fist() + ",10");

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list;
	}

	public List<Object[]> initShoppingMaterForMobile(MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT f.mater_id materId, f.mater_name materName, f.mater_type materType, b.mater_level, b.mater_value materValue, b.mater_describe materDescribe, b.mater_proportion, b.mid, b.sup_id, c.sup_companyname, b.mater_value_undressed, f.unit, f.specifications, f.brands,d.mater_num FROM t_material f, t_material_level b, t_supplier c, t_shopping_cart_mater d WHERE c.sup_id = b.sup_id AND f.mater_id = b.mater_id AND b.cust_id = 0 AND b.delete_flag = 0 AND d.cust_id = '"
				+ materVo.getCustId() + "' AND d.mater_id = f.mater_id AND b.sup_id = d.sup_id ORDER BY f.mater_name DESC ");

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list;
	}

	public List<Object[]> listSelectMaterTypeForMobile(MaterTypeVo c) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT a.`mater_type_name` ,a.`class_type`,GROUP_CONCAT( a.sup_id SEPARATOR ',' ) FROM t_material_type a WHERE EXISTS ( SELECT * FROM t_shop_supplier_type b WHERE  b.`sup_type`=a.`mater_type` AND b.`sup_id` = a.`sup_id` AND b.`shop_id`='"
				+ c.getShopId() + "'  ) GROUP BY mater_type_name   ");
		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list;
	}

	public PaginationSupport listSelectMaterForBuySupplier(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select DISTINCT f.mater_id materId,f.mater_name materName,f.mater_type materType,");
		ql.append("b.mater_level , b.mater_value materValue,b.mater_describe materDescribe,b.mater_proportion,b.mid,b.sup_id,c.sup_companyname,b.mater_value_undressed,f.unit,f.specifications,f.brands from t_material f,t_material_level b,t_supplier c where c.sup_id =b.sup_id  and f.mater_id = b.mater_id ");
		ql.append(" AND b.`cust_id`=0 ");
		ql.append(" AND b.delete_flag = 0 ");
		if (materVo != null) {
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (StringUtils.isNotBlank(materVo.getSupName())) {
				ql.append(" and c.sup_companyname like '%" + materVo.getSupName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and (f.mater_name like '%" + materVo.getMaterName().trim() + "%' OR f.`all_spell` LIKE '%" + materVo.getMaterName()
						+ "%' OR f.`fist_spell` LIKE '%" + materVo.getMaterName() + "%')");
			}
			if (materVo.getMid() != 0) {
				ql.append(" and b.mid = '" + materVo.getMid() + "'");
			}
			if (materVo.getMaterId() != 0) {
				ql.append(" and f.mater_id = '" + materVo.getMaterId() + "'");
			}
		}
		ql.append(" order by f.mater_name desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listSelectMaterSelf(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT f.mater_id materId, f.mater_name materName, f.mater_type materType, b.mater_level, b.mater_describe materDescribe, f.unit, f.specifications, f.brands FROM t_material f, t_material_level b WHERE f.mater_id = b.mater_id AND b.cust_id = 0 AND b.delete_flag = 0 ");
		if (materVo != null) {
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and (f.mater_name like '%" + materVo.getMaterName().trim() + "%' OR f.`all_spell` LIKE '%" + materVo.getMaterName()
						+ "%' OR f.`fist_spell` LIKE '%" + materVo.getMaterName() + "%')");
			}
			if (materVo.getMaterId() != 0) {
				ql.append(" and f.mater_id = '" + materVo.getMaterId() + "'");
			}
		}
		ql.append(" order by f.mater_name desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findShoppingCartMater(PageController pageController, ShoppingCartMaterVo shoppingCartMaterVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.sid, o.mater_id, o.mater_level, o.mater_value, o.mater_proportion, o.mater_describe, o.mater_value_undressed, o.sup_id, o.mater_num , m.mater_name, m.unit, m.specifications, m.brands, s.sup_companyname,o.leave_message FROM t_shopping_cart_mater o , t_material m , t_supplier s WHERE o.cust_id = '"
				+ shoppingCartMaterVo.getCustId() + "' AND m.mater_id= o.mater_id AND s.sup_id= o.sup_id");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findShoppingCartMaterSelf(PageController pageController, ShoppingCartMaterVo shoppingCartMaterVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.sid, o.mater_id, o.mater_level,o.mater_value_undressed, o.sup_id, o.mater_num , m.mater_name, m.unit, m.specifications, m.brands FROM t_shopping_cart_mater_self o , t_material m  WHERE o.cust_id = '"
				+ shoppingCartMaterVo.getCustId() + "' AND m.mater_id= o.mater_id ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findShoppingCartMaterSupplier(PageController pageController, ShoppingCartMaterVo shoppingCartMaterVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.sid, o.mater_id, o.mater_level,o.mater_value_undressed, o.sup_id, o.mater_num , m.mater_name, m.unit, m.specifications, m.brands, (SELECT bb.mater_value_undressed FROM (SELECT MAX(mater_day) AS mday, mater_id FROM t_material_level_cbl WHERE sup_id = '"
				+ shoppingCartMaterVo.getSupId()
				+ "' GROUP BY mater_id) AS aa, t_material_level_cbl bb WHERE aa.mater_id = bb.mater_id AND bb.mater_id = m.mater_id AND aa.mday = bb.mater_day AND bb.sup_id = '"
				+ shoppingCartMaterVo.getSupId()
				+ "'),o.leave_message  FROM t_shopping_cart_mater_supplier o , t_material m  WHERE o.sup_id = '"
				+ shoppingCartMaterVo.getSupId() + "' AND m.mater_id= o.mater_id ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findMaterId(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT m.mater_id,m.mater_name,m.mater_type,m.brands,m.specifications,m.unit FROM t_material m ,t_material_level ml WHERE m.mater_id = ml.mater_id AND ml.sup_id = '"
				+ materVo.getSupId() + "'");

		if (StringUtils.isNotBlank(materVo.getMaterName())) {
			ql.append(" and (m.mater_name like '%" + materVo.getMaterName().trim() + "%' OR m.`all_spell` LIKE '%" + materVo.getMaterName()
					+ "%' OR m.`fist_spell` LIKE '%" + materVo.getMaterName() + "%')");
		}
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public MaterPo listMaterPoByMaterId(String materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		FoodPo c = new FoodPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.materId=" + materId);
		List<MaterPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public MaterLevelPo listMaterLevelPoByMaterId(String materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public MaterLevelPo listMaterLevelPoByMaterIdSupIdLevelIdCustId(String materId, String supId, String materLevel, int custId)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId='" + materId + "' And supId = '" + supId + "' and materLevel = '" + materLevel
				+ "' and custId = '" + custId + "'");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<MaterLevelPo> getMaterLevelValue(int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId='" + materId + "'");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterLevelBlPo> listMaterLevelBlPoByMaterIdSupIdLevelId(MaterValueVo materValueVo) throws GenericException {

		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelBlPo o where o.materId='" + materValueVo.getMaterId() + "' And supId = '" + materValueVo.getSupId()
				+ "' and materLevel = '" + materValueVo.getMaterLevel() + "' and o.custId = '" + materValueVo.getCustId() + "'");
		List<MaterLevelBlPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterLevelCblPo> listMaterLevelBlPoForMaterCost(MaterValueVo materValueVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelCblPo o where o.materId='" + materValueVo.getMaterId() + "' And supId = '" + materValueVo.getSupId()
				+ "' and materLevel = '" + materValueVo.getMaterLevel() + "'");
		List<MaterLevelCblPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterPo> getMaterPoByMaterName(String materName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.materName='" + materName + "'");
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterPo> getMaterPoByMaterNameUnitSpecificationsBrands(String materName, String unit, String specifications, String brands)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.materName='" + materName + "' and o.unit = '" + unit + "' and o.specifications = '" + specifications
				+ "' and o.brands = '" + brands + "'");
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterPo> getMaterPoByMaterId(int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.materId='" + materId + "'");
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterPo> getMaterPo(MaterVo materVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where  1=1 ");
		if (materVo.getMaterId() != 0) {
			ql.append(" and o.materId='" + materVo.getMaterId() + "'");
		}
		if (materVo.getMaterName() != null && !"".equals(materVo.getMaterName())) {
			ql.append(" and o.materName like'%" + materVo.getMaterName() + "%'");
		}
		if (materVo.getMaterType() != 0) {
			ql.append(" and o.materType ='" + materVo.getMaterType() + "'");
		}

		if (materVo.getSupId() != 0) {
			ql.append(" and  exists( SELECT 1 from MaterLevelPo m where m.deleteFlag!=1 and m.custId=0 and m.materId= o.materId and m.supId = '"
					+ materVo.getSupId() + "' )");
		}
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterTypePo> getMaterTypePo(MaterTypeVo materTypeVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterTypePo o where  1=1 ");
		if (materTypeVo.getSupId() != 0) {
			ql.append(" AND o.supId =  '" + materTypeVo.getSupId() + "'");
		}
		if (materTypeVo.getMaterType() != 0) {
			ql.append(" AND o.materType =  '" + materTypeVo.getMaterType() + "'");
		}

		List<MaterTypePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<CheckMaterCostPo> getCheckMaterCostPo(int supId, int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from CheckMaterCostPo o where  1=1 ");
		ql.append(" and o.materId='" + materId + "'");
		ql.append(" and o.supId='" + supId + "'");
		List<CheckMaterCostPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<CheckMaterSalePo> getCheckMaterSalePo(int supId, int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from CheckMaterSalePo o where  1=1 ");
		ql.append(" and o.materId='" + materId + "'");
		ql.append(" and o.supId='" + supId + "'");
		List<CheckMaterSalePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UnitMaterConvertPo> getUnitMaterConvertPo(int supId, int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UnitMaterConvertPo o where  1=1 ");
		ql.append(" and o.auxiliaryMaterId='" + materId + "'");
		ql.append(" and o.supId='" + supId + "'");
		List<UnitMaterConvertPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UnitMaterConvertPo> getUnitMaterConvertPobyHostMaterId(int supId, int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UnitMaterConvertPo o where  1=1 ");
		ql.append(" and o.hostMaterId='" + materId + "'");
		ql.append(" and o.supId='" + supId + "'");
		List<UnitMaterConvertPo> list = template.find(ql.toString(), null);
		return list;
	}

	public BigDecimal getMaterValueCostLast(int materId, long supId) throws GenericException {
		/*
		 * StringBuffer sql = new StringBuffer(); ql.append(
		 * " SELECT bb.mater_value_undressed FROM (SELECT MAX(mater_day) AS mday, mater_id FROM t_material_level_cbl WHERE sup_id = '"
		 * + supId +
		 * "' GROUP BY mater_id) AS aa, t_material_level_cbl bb WHERE aa.mater_id = bb.mater_id AND aa.mday = bb.mater_day AND bb.sup_id = '"
		 * + supId + "' AND bb.mater_id = '" + materId + "' ");
		 * 
		 * List list =
		 * this.entityManager.createNativeQuery(sql.toString()).getResultList();
		 */
		return new BigDecimal(0);
	}

	public BigDecimal getMaterValueRealLastByMaterId(int materId, int supId, long shopId) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("  SELECT om.ord_mater_value_unit FROM t_order_supplier o, t_order_supplier_mater_reality om WHERE o.ord_id = om.ord_id AND o.ord_cust_id = '"
				+ shopId
				+ "' AND o.ord_sup_id = '"
				+ supId
				+ "' AND o.ord_sup_id=om.ord_sup_id AND om.ord_mater_id ='"
				+ materId
				+ "' AND o.ord_date=(SELECT MAX(o.ord_date) FROM t_order_supplier o, t_order_supplier_mater_reality om WHERE o.ord_id = om.ord_id AND o.ord_cust_id = '"
				+ shopId
				+ "' AND o.ord_sup_id = '"
				+ supId
				+ "' AND o.ord_state != 3 AND o.ord_sup_id=om.ord_sup_id AND om.ord_mater_id ='"
				+ materId + "' AND om.ord_mater_value_unit IS NOT NULL AND om.ord_mater_value_unit != 0 ) ");

		List<Object[]> list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		if (list != null && list.size() > 0) {
			Object a = list.get(0);
			if (a != null) {
				return new BigDecimal(a.toString());
			} else {
				return new BigDecimal(0);
			}
		} else {
			return new BigDecimal(0);
		}
	}

	public List<MaterPo> getMaterPoByBarcode(String barcode) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.barcode='" + barcode + "'");
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<CheckMaterCostPo> getCheckMaterCostPoById(int id) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from CheckMaterCostPo o where o.id='" + id + "'");
		List<CheckMaterCostPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<CheckMaterSalePo> getCheckMaterSalePoById(int id) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from CheckMaterSalePo o where o.id='" + id + "'");
		List<CheckMaterSalePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShoppingCartMaterPo> getShoppingCartMaterPo(int materId, long custId, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShoppingCartMaterPo o where o.materId='" + materId + "' AND o.custId = '" + custId + "' AND o.supId = '" + supId + "' ");
		List<ShoppingCartMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShoppingCartMaterPo> getShoppingCartMaterPo(long custId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShoppingCartMaterPo o where  o.custId = '" + custId + "'");
		List<ShoppingCartMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShoppingCartMaterSelfPo> getShoppingCartMaterSelfPo(int materId, long custId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShoppingCartMaterSelfPo o where o.materId='" + materId + "' AND o.custId = '" + custId + "'");
		List<ShoppingCartMaterSelfPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShoppingCartMaterSupplierPo> getShoppingCartMaterSupPo(int materId, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShoppingCartMaterSupplierPo o where o.materId='" + materId + "' AND o.supId = '" + supId + "'");
		List<ShoppingCartMaterSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterLevelPo> getMaterLevelPoByMaterNameAndLevel(String materName, String Level, int supId) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT 1 FROM t_material a , t_material_level b WHERE a.mater_id = b.mater_id AND a.mater_name = '" + materName
						+ "' AND b.mater_level = '" + Level + "' and b.sup_id = '" + supId + "'").getResultList();
		return list;
	}

	public List<MaterLevelPo> getMaterLevelPoByMaterNameUnitSpecificationsBrandsSupid(String materName, String unit, String specifications,
			String brands, int supId) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT a.mater_id FROM t_material a , t_material_level b WHERE a.mater_id = b.mater_id AND a.mater_name = '" + materName
						+ "' AND a.unit = '" + unit + "' and a.specifications = '" + specifications + "'and a.brands='" + brands + "'and b.sup_id ='"
						+ supId + "'").getResultList();
		return list;
	}

	public List<Integer> getMaterLevelPoByMaterNameUnitSpecificationsBrandsSupidForUpdate(String materName, String unit, String specifications,
			String brands, int supId, int materType) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT a.mater_id FROM t_material a , t_material_level b WHERE a.mater_id = b.mater_id AND a.mater_name = '" + materName
						+ "' AND a.unit = '" + unit + "' and a.specifications = '" + specifications + "'and a.brands='" + brands + "'and b.sup_id ='"
						+ supId + "' and a.mater_type ='" + materType + "'").getResultList();
		return list;
	}

	@Override
	public List<MaterLevelPo> getMaterLevelPoByMaterIdAndLevelAndSupId(int materId, int Level, int supId, String custId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId='" + materId + "' and o.supId='" + supId + "' and o.custId = '" + custId + "'");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object> getMaterTypeBySupId(long supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT b.mater_type FROM t_material_level a, t_material b WHERE a.mater_id = b.mater_id AND a.sup_id = '" + supId + "' ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	@Override
	public List<MaterLevelPo> getMaterLevelPoByMaterId(int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId='" + materId + "'");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterLevelBlPo> getMaterLevelBlPoByMaterIdAndLevelAndSupId(int materId, int Level, int supId, String materDay, String custId)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelBlPo o where o.materId='" + materId + "' and o.supId='" + supId + "' and o.materDay = '" + materDay
				+ "' AND o.custId = '" + custId + "'");
		List<MaterLevelBlPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterLevelCblPo> getMaterLevelCblPoByMaterIdAndSupIdAndMaterDay(int materId, int supId, String day) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelCblPo o where o.materId='" + materId + "' and o.supId='" + supId + "' and o.materDay = '" + day + "' ");
		List<MaterLevelCblPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterLevelBlPo> getMaterLevelBlPoByMaterIdAndSupIdAndMaterDay(int materId, int supId, String materDay) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelBlPo o where o.materId='" + materId + "' and o.supId='" + supId + "' and o.materDay = '" + materDay + "'");
		List<MaterLevelBlPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public int getMidMaterLevelByMaterId(String materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0).getMid();
	}

	@Override
	public List<MaterLevelPo> getMidMaterLevelListByMaterId(String materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<MaterLevelPo> getAllMaterLevelList() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o ");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<MaterPo> getAllMaterList() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o ");
		List<MaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object[]> getAllExcelMater() throws GenericException {

		List list = this.entityManager
				.createNativeQuery(
						"  SELECT b.mater_name, b.mater_type, c.sup_companyname, a.mater_level, a.mater_value_undressed, a.mater_proportion, a.mater_describe FROM t_material_level a, t_material b, t_supplier c WHERE a.mater_id = b.mater_id AND c.sup_id = a.sup_id ")
				.getResultList();
		return list;
	}

	public List<Object[]> getAllExcelMaterCust(long userId) throws GenericException {

		List list = this.entityManager
				.createNativeQuery(
						"SELECT b.mid, f.mater_name materName FROM t_material f, t_material_level b, t_supplier c WHERE c.sup_id = b.sup_id AND f.mater_id = b.mater_id AND EXISTS (SELECT 1 FROM t_user_supplier d WHERE d.`user_id` = '"
								+ userId + "' AND d.`sup_type` = f.`mater_type` AND b.`sup_id` = d.`sup_id`) ORDER BY f.mater_name DESC ")
				.getResultList();
		return list;
	}
}
